using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using QuanLySim.DTO;

namespace QuanLySim.DAO
{
    class clsSimDAO
    {
        #region "variable"
        private string m_Erorr;
        private clsCommand m_Command;
        #endregion

        #region "property"
        public string ERORR
        {
            get { return m_Erorr; }
            set { m_Erorr = value; }
        }
        #endregion

        #region "method"
        public clsSimDAO()
        {
            m_Erorr = "";
            m_Command = new clsCommand();
        }

        public bool Insert(clsSimDTO SimDto)
        {
            m_Command = new clsCommand();
            string strInsert = "INSERT INTO SIM (SODIENTHOAI, DAUSO, GIATRI, NGAYTHEM, TENDANGNHAP, MATEN) VALUES (";
            strInsert += "'" + SimDto.SODIENTHOAI + "',";
            strInsert += "'" + SimDto.DAUSO + "',";
            strInsert += "'" + SimDto.GIATRI + "',";
            strInsert += "'" + SimDto.NGAYTHEM + "',";
            strInsert += "'" + SimDto.TENDANGNHAP + "',";
            strInsert += "'" + SimDto.MATEN + "')";

            return m_Command.ExecuteNonQuery(strInsert);
        }

        public bool Delete(clsSimDTO SimDto)
        {
            m_Command = new clsCommand();
            string strDelete = "DELETE FROM SIM WHERE SODIENTHOAI = ";
            strDelete += "'" + SimDto.SODIENTHOAI + "'";

            return m_Command.ExecuteNonQuery(strDelete);
        }

        public bool Update(clsSimDTO SimDto)
        {
            m_Command = new clsCommand();
            string strUpdate = "UPDATE SIM SET";
            strUpdate += " GIATRI = '" + SimDto.GIATRI + "',";
            strUpdate += " MATEN = '" + SimDto.MATEN + "'";
            strUpdate += " WHERE SODIENTHOAI = '" + SimDto.SODIENTHOAI + "'";

            return m_Command.ExecuteNonQuery(strUpdate);
        }

        public DataTable Sim()
        {
            m_Command = new clsCommand();
            DataTable table = new DataTable();
            string strSelect = "SELECT SODIENTHOAI, TENMANG, GIATRI, NGAYTHEM, TENNGUOITHEM, TENGOI FROM SIM, QUANLY, DAUSO, TENSIM";
            strSelect += " WHERE SIM.TENDANGNHAP = QUANLY.TENDANGNHAP AND DAUSO.DAUSO = SIM.DAUSO AND SIM.MATEN = TENSIM.MATEN";
            if (m_Command.Connect())
            {
                table = m_Command.Select(strSelect);
                m_Command.CloseConnect();
                return table;
            }
            m_Erorr = m_Command.ERORR;
            return null;
        }

        public DataTable TenGoi()
        {
            m_Command = new clsCommand();
            DataTable table = new DataTable();
            string strSelect = "SELECT TENGOI FROM TENSIM";
            if (m_Command.Connect())
            {
                table = m_Command.Select(strSelect);
                m_Command.CloseConnect();
                return table;
            }
            m_Erorr = m_Command.ERORR;
            return null;
        }

        public DataTable MaTen(string strTenGoi)
        {
            m_Command = new clsCommand();
            DataTable table = new DataTable();
            string strSelect = "SELECT MATEN FROM TENSIM WHERE TENGOI = '" + strTenGoi + "'";
            if (m_Command.Connect())
            {
                table = m_Command.Select(strSelect);
                m_Command.CloseConnect();
                return table;
            }
            m_Erorr = m_Command.ERORR;
            return null;
        }

        public DataTable TimSim(clsSimDTO SimDto)
        {
            m_Command = new clsCommand();
            DataTable table = new DataTable();
            string strSelect = "SELECT SODIENTHOAI, TENMANG, GIATRI, NGAYTHEM, TENNGUOITHEM, TENGOI FROM SIM, QUANLY, DAUSO, TENSIM";
            strSelect += " WHERE SIM.TENDANGNHAP = QUANLY.TENDANGNHAP AND DAUSO.DAUSO = SIM.DAUSO AND SIM.MATEN = TENSIM.MATEN";
            if (SimDto.TENMANG != "")
            {
                strSelect += " AND TENMANG = '" + SimDto.TENMANG + "'";
            }
            if (SimDto.SODIENTHOAI != "")
            {
                strSelect += " AND SODIENTHOAI = '" + SimDto.SODIENTHOAI + "'";
            }
            if (SimDto.DAUSO != "")
            {
                strSelect += " AND DAUSO.DAUSO = '" + SimDto.DAUSO + "'";
            }
            if (SimDto.GIATRI != "")
            {
                strSelect += " AND GIATRI = '" + SimDto.GIATRI + "'";
            }
            if (SimDto.TENGOI != "")
            {
                strSelect += " AND TENGOI = '" + SimDto.TENGOI + "'";
            }

            if (m_Command.Connect())
            {
                table = m_Command.Select(strSelect);
                m_Command.CloseConnect();
                return table;
            }
            m_Erorr = m_Command.ERORR;
            return null;
        }
        #endregion
    }
}
